package tr.com.argela.tap.mot.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import tr.com.argela.tap.mot.logger.LoggerFactory;
import tr.com.argela.tap.mot.models.Module;
import tr.com.argela.tap.mot.models.TestPoint;
import tr.com.argela.tap.mot.models.User;

/**
 * @author arge
 */
public class TestPointDao {

    private static Logger logger = LoggerFactory.getLogger("servlets");

    public TestPointDao() {

    }

    public TestPoint checkpoint(Long id) {
        TestPoint point = null;
        PreparedStatement stmt = null;

        String sql = "select p.id as test_point_id, p.name as test_point_name, p.description,u.name as username,"
                + "u.id as user_id, m.name as module_name, m.id as module_id from test_point p, user u,module m where p.id = ? and u.id = p.user_id and m.id = p.module_id";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            point = new TestPoint();

            while (rs.next()) {
                point.setId(rs.getLong("test_point_id"));
                point.setName(rs.getString("test_point_name"));

                Module module = new Module();
                module.setName(rs.getString("module_name"));
                module.setId(rs.getLong("module_id"));
                point.setModule(module);
                User user = new User();
                user.setName(rs.getString("username"));
                user.setId(rs.getLong("user_id"));
                point.setUser(user);
                point.setDescription(rs.getString("description"));

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (point == null) {
            System.out.println("Test Point is null: ");
        }
        return point;

    }

    public TestPoint getTestPointById(Long id) {
        TestPoint point = null;
        PreparedStatement stmt = null;

        String sql = "select * from test_point where id = ?";
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            point = new TestPoint();

            while (rs.next()) {
                point.setId(rs.getLong("id"));
                point.setName(rs.getString("name"));

                Module module = new Module();

                module.setId(rs.getLong("module_id"));
                point.setModule(module);
                User user = new User();

                user.setId(rs.getLong("user_id"));
                point.setUser(user);
                point.setDescription(rs.getString("description"));

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (point == null) {
            System.out.println("Test Point is null: ");
        }
        return point;

    }

    public List<TestPoint> seepoint(Long module_id) {
        TestPoint point = null;
        PreparedStatement stmt = null;
        List<TestPoint> pointlist = new ArrayList<TestPoint>();
        String sql = "select * from test_point where module_id= ?";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, module_id);
            rs = stmt.executeQuery();

            while (rs.next()) {
                point = new TestPoint();
                point.setId(rs.getLong("id"));
                point.setName(rs.getString("name"));

                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                point.setModule(module);
                User user = new User();
                user.setId(rs.getLong("user_id"));
                point.setUser(user);
                point.setDescription(rs.getString("description"));
                pointlist.add(point);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (point == null) {
            System.out.println("Project is null: ");
        }
        return pointlist;

    }

    public List<TestPoint> seepoint2(Long user_id) {
        TestPoint point = null;
        PreparedStatement stmt = null;
        List<TestPoint> pointlist = new ArrayList<TestPoint>();
        String sql = "select * from test_point where user_id= ?";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, user_id);
            rs = stmt.executeQuery();

            while (rs.next()) {
                point = new TestPoint();
                point.setId(rs.getLong("id"));
                point.setName(rs.getString("name"));

                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                point.setModule(module);
                User user = new User();
                user.setId(rs.getLong("user_id"));
                point.setUser(user);
                point.setDescription(rs.getString("description"));
                pointlist.add(point);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (point == null) {
            System.out.println("Project is null: ");
        }
        return pointlist;

    }

    public int UpdateTpoint(DbManager dbManager, Long pointId, String name, Module module, User user, String desc) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "update test_point set  name = ?, module_id = ?, user_id = ?, description = ? where id = ? ";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);

            stmt.setString(1, name);
            stmt.setLong(2, module.getId());
            stmt.setLong(3, user.getId());
            stmt.setString(4, desc);

            stmt.setLong(5, pointId);
            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        return effectedRows;
    }

    public List<TestPoint> seepointlist() {
        TestPoint point = null;
        PreparedStatement stmt = null;
        List<TestPoint> pointlist = new ArrayList<TestPoint>();
        String sql = "select * from test_point";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);

            rs = stmt.executeQuery();

            while (rs.next()) {
                point = new TestPoint();
                point.setId(rs.getLong("id"));
                point.setName(rs.getString("name"));

                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                point.setModule(module);
                User user = new User();
                user.setId(rs.getLong("user_id"));
                point.setUser(user);
                point.setDescription(rs.getString("description"));
                pointlist.add(point);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (point == null) {
            System.out.println("Project is null: ");
        }
        return pointlist;

    }

    public int addTestPoint(DbManager dbManager, String name, Module module, User user, String desc) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "insert into test_point (name,module_id,user_id,description) values (?,?,?,?)";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setLong(2, module.getId());
            stmt.setLong(3, user.getId());
            stmt.setString(4, desc);

            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        return effectedRows;
    }

}
